package com.ztesoft.zcm.cmdb.util;

import com.ztesoft.zsmart.core.exception.BaseAppException;
import com.ztesoft.zsmart.core.log.ZSmartLogger;
import com.ztesoft.zsmart.zcm.core.exception.ExceptionPublisher;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Description: 读excel帮助类<br>
 *
 * @author yan.qicui<br>
 * #date 2018年07月08日<br>
 * @since V1.0<br>
 */
public class ReadExcelHelper {
    /** 日志 */
    private static final ZSmartLogger logger = ZSmartLogger.getLogger(ReadExcelHelper.class);
    public ReadExcelHelper(Workbook workbook) {
    }

    /**
     * 读取一个sheet页或sheet信息
     *
     * @param sheet        <br>
     * @param startKeyName 开始读取excel信息的起始坐标的关键字<br>
     * @param className    <br>
     * @return <br>
     * @throws BaseAppException          <br>
     * @throws ClassNotFoundException    <br>
     * @throws IllegalAccessException    <br>
     * @throws InstantiationException    <br>
     * @throws InvocationTargetException <br>
     */
    public List<Object> readSheetInfo(Sheet sheet, String startKeyName, String className)
            throws BaseAppException, ClassNotFoundException, IllegalAccessException, InstantiationException, InvocationTargetException {
        logger.info("startKeyName is {0}, sheet name {1}, class name is {2}", startKeyName, sheet.getSheetName(), className);
        int[] coordinate = CommonExcelHelper.getStartCoordinate(sheet, startKeyName);
        return readSheetInfo(sheet, coordinate, className);
    }

    /**
     * 读操作系统基本信息标签
     *
     * @param sheet <br>
     */
    private List<Object> readSheetInfo(Sheet sheet, int[] coordinate, String className)
            throws BaseAppException, ClassNotFoundException, IllegalAccessException, InstantiationException, InvocationTargetException {
        //从标题行下一行开始读
        int rowFirst = coordinate[0] + 1;
        int rowLast = sheet.getLastRowNum();

        Class<?> clazz = Class.forName(className);
        List<Method> methodList = new ArrayList<>();
        Method[] declaredMethodArr = clazz.getDeclaredMethods();
        methodList.addAll(Arrays.asList(declaredMethodArr));
        while (clazz != null && clazz != Object.class) {
            methodList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredMethods())));
            clazz = clazz.getSuperclass();
        }
        Map<String, Method> methodMap = new HashMap<>();
        for (Method method : methodList) {
            methodMap.put(method.getName().toLowerCase(), method);
        }

        String sheetName = sheet.getSheetName();
        List sheetObjList = new ArrayList(rowLast);
        Row titleRow = sheet.getRow(coordinate[0]);
        for (int i = rowFirst; i <= rowLast; i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                logger.warn("row is null, row number is: {0}", i);
                continue;
            }
            int cellLast = row.getLastCellNum();
            if (cellLast == -1) {
                logger.warn("cellLast is -1");
                continue;
            }
            int cellFirst = coordinate[1];
            if (!checkEmptyRow(cellFirst, row)) {
                logger.warn("empty row, sheet name is {0}, row number is {1}", sheetName, i);
                continue;
            }
            Class<?> clz = Class.forName(className);
            Object obj = clz.newInstance();
            for (int j = cellFirst; j < cellLast; j++) {
                Cell cell = row.getCell(j);
                String cellStr = CommonExcelHelper.getCellString(cell);
                String titleCellStr = CommonExcelHelper.getCellString(titleRow.getCell(j));

                String title = titleCellStr.toLowerCase().replaceAll("_", "").replaceAll(" ", "");
                if (StringUtils.isEmpty(title)) {
                    logger.debug("title is empty.");
                    continue;
                }
                String getMethodName = "get" + title;
                String setMethodName = "set" + title;
                Method setMmethod = methodMap.get(setMethodName);

                if (null == setMmethod) {
                    logger.error("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@method is empty[{0}] sheet name is {1}", setMethodName, sheetName);
                    continue;
                }
                boolean mergedRegion = CommonExcelHelper.isInMergedRegion(sheet, i, j);
                if (mergedRegion && StringUtils.isEmpty(cellStr)) {
                    if (null != sheetObjList) {
                        try {
                            Object lastRowObj = sheetObjList.get(sheetObjList.size() - 1);
                            Method getMmethod = methodMap.get(getMethodName);
                            cellStr = (String) getMmethod.invoke(lastRowObj);
                        }
                        catch (IndexOutOfBoundsException e) {
                            logger.error("IndexOutOfBoundsException", e);
                        }
                    }
                }
                if (!StringUtils.isEmpty(cellStr)) {
                    Class<?>[] clazzes = setMmethod.getParameterTypes();
                    for (Class<?> claz : clazzes) {
                        if (claz.equals(Date.class)) {
                            setMmethod.invoke(obj, DateUtil.string2Date(cellStr, null));
                        }
                        else if (claz.equals(Integer.class)) {
                            setMmethod.invoke(obj, Integer.parseInt(cellStr));
                        }
                        else {
                            setMmethod.invoke(obj, cellStr);
                        }
                    }
                }
            }
            sheetObjList.add(obj);
        }
        logger.debug("================================" + sheetName + " end=====================================");
        return sheetObjList;
    }

    /**
     * 检测一行中是否全部是空的单元格
     *
     * @param startCol 起始列
     * @param checkRow 检测的行
     * @return <br>
     * @throws BaseAppException <br>
     */
    private boolean checkEmptyRow(int startCol, Row checkRow) throws BaseAppException {
        short lastCellNum = checkRow.getLastCellNum();
        if (startCol > lastCellNum) {
            ExceptionPublisher.publish("ZCM-00000", "start index can not bigger than end.");
        }
        for (int j = startCol; j < lastCellNum; j++) {
            String cellString = CommonExcelHelper.getCellString(checkRow.getCell(j));
            if (StringUtils.isNotEmpty(cellString.trim())) {
                return true;
            }
        }
        return false;
    }

}